USE [master]
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'ProjectManager')
BEGIN
CREATE DATABASE [ProjectManager]
END
GO

USE [ProjectManager]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_TimesheetEntry_Task]') AND parent_object_id = OBJECT_ID(N'[TimesheetEntry]'))
ALTER TABLE [TimesheetEntry] DROP CONSTRAINT [FK_TimesheetEntry_Task]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_TimesheetEntry_User]') AND parent_object_id = OBJECT_ID(N'[TimesheetEntry]'))
ALTER TABLE [TimesheetEntry] DROP CONSTRAINT [FK_TimesheetEntry_User]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TimesheetEntry]') AND type in (N'U'))
DROP TABLE [TimesheetEntry]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[User]') AND type in (N'U'))
DROP TABLE [User]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Task]') AND type in (N'U'))
DROP TABLE [Task]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Task]') AND type in (N'U'))
BEGIN
CREATE TABLE [Task](
	[TaskGuid] [uniqueidentifier] NOT NULL,
	[TaskLevel] [smallint] NULL,
	[TaskDomain] [nvarchar](1000) NULL,
	[Title] [nvarchar](1000) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[ParentTask] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
 (
	[TaskGuid] ASC
 )
)
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[User]') AND type in (N'U'))
BEGIN
CREATE TABLE [User](
	[UserName] [nvarchar](50) NOT NULL,
	[Password] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
 (
	[UserName] ASC
 ) 
)
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TimesheetEntry]') AND type in (N'U'))
BEGIN
CREATE TABLE [TimesheetEntry](
	[TimesheetEntryGuid] [uniqueidentifier] NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Start] [datetime] NOT NULL,
	[Finish] [datetime] NULL,
	[Comment] [nvarchar](max) NULL,
	[Break] [datetime] NULL,
	[Task] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_TimesheetEntry] PRIMARY KEY CLUSTERED 
 (
	[TimesheetEntryGuid] ASC
 )
)
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_TimesheetEntry_Task]') AND parent_object_id = OBJECT_ID(N'[TimesheetEntry]'))
ALTER TABLE [TimesheetEntry]  WITH CHECK ADD  CONSTRAINT [FK_TimesheetEntry_Task] FOREIGN KEY([Task])
REFERENCES [Task] ([TaskGuid])
GO

ALTER TABLE [TimesheetEntry] CHECK CONSTRAINT [FK_TimesheetEntry_Task]
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_TimesheetEntry_User]') AND parent_object_id = OBJECT_ID(N'[TimesheetEntry]'))
ALTER TABLE [TimesheetEntry]  WITH CHECK ADD  CONSTRAINT [FK_TimesheetEntry_User] FOREIGN KEY([UserName])
REFERENCES [User] ([UserName])
GO

ALTER TABLE [TimesheetEntry] CHECK CONSTRAINT [FK_TimesheetEntry_User]
GO
